{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Knowledge Graph Embedding using the Wikimedia knowledge graph.\n",
    "\n",
    "In this notebook, we will show using the Wikimedia knowledge graph data, how you can compute embeddings for your knowledge graph and perform downstream task completion such as node classification and link prediction.\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DGL-KE Installation\n",
    "The dglke_score function is not part of the pipy dglke package yet and we need to follow the instructions from the [documentation](https://aws-dglke.readthedocs.io/en/latest/install.html) to build dgl-ke from source.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!sudo pip3 install dgl-cu101\n",
    "!git clone  https://github.com/awslabs/dgl-ke.git\n",
    "!pushd dgl-ke;cd python;sudo python3 setup.py install;\n",
    "!pip3 uninstall torch -y\n",
    "!sudo pip3 install torch==1.5.0+cu101 torchvision==0.6.0+cu101 -f https://download.pytorch.org/whl/torch_stable.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Preparation\n",
    "First, you can download the data from kaggle using this link [here](https://www.kaggle.com/kenshoresearch/kensho-derived-wikimedia-data). \n",
    "\n",
    "The zip file is 24GB in total. For our example, we only need the property.csv, item.csv and statements.csv.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Once you have downloaded the file, you can unzip the files in the ./data/wikimedia/ folder."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "jupyter": {
     "outputs_hidden": true,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "!unzip <file>.zip -d ./data/wikimedia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "\n",
    "%matplotlib notebook\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "import numpy as np\n",
    "import pylab as plt\n",
    "#import seaborn as sns\n",
    "#sns.set_style('ticks')\n",
    "\n",
    "#import seaborn as sns\n",
    "import os\n",
    "import json\n",
    "import boto3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's load the relation dictionary file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>property_id</th>\n",
       "      <th>en_label</th>\n",
       "      <th>en_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>head of government</td>\n",
       "      <td>head of the executive power of this town, city...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>video</td>\n",
       "      <td>relevant video. For images, use the property P...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>traffic sign</td>\n",
       "      <td>graphic symbol describing the item, used at th...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>route map</td>\n",
       "      <td>image of route map at Wikimedia Commons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>highway system</td>\n",
       "      <td>system (or specific country specific road type...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   property_id            en_label  \\\n",
       "0            6  head of government   \n",
       "1           10               video   \n",
       "2           14        traffic sign   \n",
       "3           15           route map   \n",
       "4           16      highway system   \n",
       "\n",
       "                                      en_description  \n",
       "0  head of the executive power of this town, city...  \n",
       "1  relevant video. For images, use the property P...  \n",
       "2  graphic symbol describing the item, used at th...  \n",
       "3            image of route map at Wikimedia Commons  \n",
       "4  system (or specific country specific road type...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfRelations = pd.read_csv(\"./data/wikimedia/property.csv\")\n",
    "dfRelations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "property_id       6985\n",
       "en_label          6985\n",
       "en_description    6905\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Total Counts\n",
    "dfRelations.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Let's find out if we have any zeros. Looks like some properties do not have descriptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "property_id        0\n",
       "en_label           0\n",
       "en_description    80\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfRelations.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's load the entity file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_id</th>\n",
       "      <th>en_label</th>\n",
       "      <th>en_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Earth</td>\n",
       "      <td>third planet from the Sun in the Solar System</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>life</td>\n",
       "      <td>matter capable of extracting energy from the e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>death</td>\n",
       "      <td>permanent cessation of vital functions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>human</td>\n",
       "      <td>common name of Homo sapiens, unique extant spe...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item_id  en_label                                     en_description\n",
       "0        1  Universe                 totality of space and all contents\n",
       "1        2     Earth      third planet from the Sun in the Solar System\n",
       "2        3      life  matter capable of extracting energy from the e...\n",
       "3        4     death             permanent cessation of vital functions\n",
       "4        5     human  common name of Homo sapiens, unique extant spe..."
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfEntities = pd.read_csv(\"./data/wikimedia/item.csv\")\n",
    "dfEntities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "item_id           51450316\n",
       "en_label          43232907\n",
       "en_description    38507973\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Total Counts\n",
    "dfEntities.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "item_id                  0\n",
       "en_label           8217409\n",
       "en_description    12942343\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Null Counts\n",
    "dfEntities.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop description column.\n",
    "dfEntitiesClean = dfEntities.drop('en_description', 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop any null values.\n",
    "dfEntitiesClean = dfEntities.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "item_id           34373175\n",
       "en_label          34373175\n",
       "en_description    34373175\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfEntitiesClean.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's load the statements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_item_id</th>\n",
       "      <th>edge_property_id</th>\n",
       "      <th>target_item_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>36906466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>279</td>\n",
       "      <td>3695190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>497745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>1133705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>1139177</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   source_item_id  edge_property_id  target_item_id\n",
       "0               1                31        36906466\n",
       "1               1               279         3695190\n",
       "2               1               398          497745\n",
       "3               1               398         1133705\n",
       "4               1               398         1139177"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfTriple = pd.read_csv(\"./data/wikimedia/statements.csv\")\n",
    "dfTriple.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's join the properties and the statements file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfResults=pd.merge(dfTriple, dfRelations, left_on='edge_property_id', right_on='property_id')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_item_id</th>\n",
       "      <th>edge_property_id</th>\n",
       "      <th>target_item_id</th>\n",
       "      <th>property_id</th>\n",
       "      <th>en_label</th>\n",
       "      <th>en_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>36906466</td>\n",
       "      <td>31</td>\n",
       "      <td>instance of</td>\n",
       "      <td>that class of which this subject is a particul...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>3504248</td>\n",
       "      <td>31</td>\n",
       "      <td>instance of</td>\n",
       "      <td>that class of which this subject is a particul...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>937228</td>\n",
       "      <td>31</td>\n",
       "      <td>instance of</td>\n",
       "      <td>that class of which this subject is a particul...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>2996394</td>\n",
       "      <td>31</td>\n",
       "      <td>instance of</td>\n",
       "      <td>that class of which this subject is a particul...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>55983715</td>\n",
       "      <td>31</td>\n",
       "      <td>instance of</td>\n",
       "      <td>that class of which this subject is a particul...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   source_item_id  edge_property_id  target_item_id  property_id     en_label  \\\n",
       "0               1                31        36906466           31  instance of   \n",
       "1               2                31         3504248           31  instance of   \n",
       "2               3                31          937228           31  instance of   \n",
       "3               4                31         2996394           31  instance of   \n",
       "4               5                31        55983715           31  instance of   \n",
       "\n",
       "                                      en_description  \n",
       "0  that class of which this subject is a particul...  \n",
       "1  that class of which this subject is a particul...  \n",
       "2  that class of which this subject is a particul...  \n",
       "3  that class of which this subject is a particul...  \n",
       "4  that class of which this subject is a particul...  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfResults.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's join the entity and the head columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfResultsHR=pd.merge(dfResults, dfEntities, left_on='source_item_id', right_on='item_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_item_id</th>\n",
       "      <th>edge_property_id</th>\n",
       "      <th>target_item_id</th>\n",
       "      <th>property_id</th>\n",
       "      <th>en_label_x</th>\n",
       "      <th>en_description_x</th>\n",
       "      <th>item_id</th>\n",
       "      <th>en_label_y</th>\n",
       "      <th>en_description_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>36906466</td>\n",
       "      <td>31</td>\n",
       "      <td>instance of</td>\n",
       "      <td>that class of which this subject is a particul...</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>279</td>\n",
       "      <td>3695190</td>\n",
       "      <td>279</td>\n",
       "      <td>subclass of</td>\n",
       "      <td>all instances of these items are instances of ...</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>497745</td>\n",
       "      <td>398</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>minor body that belongs to the item</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>1133705</td>\n",
       "      <td>398</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>minor body that belongs to the item</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>1139177</td>\n",
       "      <td>398</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>minor body that belongs to the item</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   source_item_id  edge_property_id  target_item_id  property_id  \\\n",
       "0               1                31        36906466           31   \n",
       "1               1               279         3695190          279   \n",
       "2               1               398          497745          398   \n",
       "3               1               398         1133705          398   \n",
       "4               1               398         1139177          398   \n",
       "\n",
       "                en_label_x                                   en_description_x  \\\n",
       "0              instance of  that class of which this subject is a particul...   \n",
       "1              subclass of  all instances of these items are instances of ...   \n",
       "2  child astronomical body                minor body that belongs to the item   \n",
       "3  child astronomical body                minor body that belongs to the item   \n",
       "4  child astronomical body                minor body that belongs to the item   \n",
       "\n",
       "   item_id en_label_y                    en_description_y  \n",
       "0        1   Universe  totality of space and all contents  \n",
       "1        1   Universe  totality of space and all contents  \n",
       "2        1   Universe  totality of space and all contents  \n",
       "3        1   Universe  totality of space and all contents  \n",
       "4        1   Universe  totality of space and all contents  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfResultsHR.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's join the entity and the tail columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfResultsHRT=pd.merge(dfResultsHR, dfEntities,how='left', left_on='target_item_id', right_on='item_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_item_id</th>\n",
       "      <th>edge_property_id</th>\n",
       "      <th>target_item_id</th>\n",
       "      <th>property_id</th>\n",
       "      <th>en_label_x</th>\n",
       "      <th>en_description_x</th>\n",
       "      <th>item_id_x</th>\n",
       "      <th>en_label_y</th>\n",
       "      <th>en_description_y</th>\n",
       "      <th>item_id_y</th>\n",
       "      <th>en_label</th>\n",
       "      <th>en_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>36906466</td>\n",
       "      <td>31</td>\n",
       "      <td>instance of</td>\n",
       "      <td>that class of which this subject is a particul...</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "      <td>36906466.0</td>\n",
       "      <td>universe</td>\n",
       "      <td>class of which Our Universe is an instance, an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>279</td>\n",
       "      <td>3695190</td>\n",
       "      <td>279</td>\n",
       "      <td>subclass of</td>\n",
       "      <td>all instances of these items are instances of ...</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "      <td>3695190.0</td>\n",
       "      <td>cosmology</td>\n",
       "      <td>discipline directed to the philosophical conte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>497745</td>\n",
       "      <td>398</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>minor body that belongs to the item</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "      <td>497745.0</td>\n",
       "      <td>Sloan Great Wall</td>\n",
       "      <td>one of the largest known structures in the uni...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>1133705</td>\n",
       "      <td>398</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>minor body that belongs to the item</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "      <td>1133705.0</td>\n",
       "      <td>galaxy filament</td>\n",
       "      <td>thread-like structures that form the boundarie...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>398</td>\n",
       "      <td>1139177</td>\n",
       "      <td>398</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>minor body that belongs to the item</td>\n",
       "      <td>1</td>\n",
       "      <td>Universe</td>\n",
       "      <td>totality of space and all contents</td>\n",
       "      <td>1139177.0</td>\n",
       "      <td>CfA2 Great Wall</td>\n",
       "      <td>one of the largest known superstructures in th...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   source_item_id  edge_property_id  target_item_id  property_id  \\\n",
       "0               1                31        36906466           31   \n",
       "1               1               279         3695190          279   \n",
       "2               1               398          497745          398   \n",
       "3               1               398         1133705          398   \n",
       "4               1               398         1139177          398   \n",
       "\n",
       "                en_label_x                                   en_description_x  \\\n",
       "0              instance of  that class of which this subject is a particul...   \n",
       "1              subclass of  all instances of these items are instances of ...   \n",
       "2  child astronomical body                minor body that belongs to the item   \n",
       "3  child astronomical body                minor body that belongs to the item   \n",
       "4  child astronomical body                minor body that belongs to the item   \n",
       "\n",
       "   item_id_x en_label_y                    en_description_y   item_id_y  \\\n",
       "0          1   Universe  totality of space and all contents  36906466.0   \n",
       "1          1   Universe  totality of space and all contents   3695190.0   \n",
       "2          1   Universe  totality of space and all contents    497745.0   \n",
       "3          1   Universe  totality of space and all contents   1133705.0   \n",
       "4          1   Universe  totality of space and all contents   1139177.0   \n",
       "\n",
       "           en_label                                     en_description  \n",
       "0          universe  class of which Our Universe is an instance, an...  \n",
       "1         cosmology  discipline directed to the philosophical conte...  \n",
       "2  Sloan Great Wall  one of the largest known structures in the uni...  \n",
       "3   galaxy filament  thread-like structures that form the boundarie...  \n",
       "4   CfA2 Great Wall  one of the largest known superstructures in th...  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfResultsHRT.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's select and rename the head, relation and tail to [h,r,t] columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfResultsFinal = dfResultsHRT[['en_label_y','en_label_x', 'en_label']].rename(columns={\"en_label_y\": \"head\", \"en_label_x\": \"relation\", \"en_label\":\"tail\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>head</th>\n",
       "      <th>relation</th>\n",
       "      <th>tail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Universe</td>\n",
       "      <td>instance of</td>\n",
       "      <td>universe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Universe</td>\n",
       "      <td>subclass of</td>\n",
       "      <td>cosmology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Universe</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>Sloan Great Wall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Universe</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>galaxy filament</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Universe</td>\n",
       "      <td>child astronomical body</td>\n",
       "      <td>CfA2 Great Wall</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       head                 relation              tail\n",
       "0  Universe              instance of          universe\n",
       "1  Universe              subclass of         cosmology\n",
       "2  Universe  child astronomical body  Sloan Great Wall\n",
       "3  Universe  child astronomical body   galaxy filament\n",
       "4  Universe  child astronomical body   CfA2 Great Wall"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfResultsFinal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "head        20788004\n",
       "relation           0\n",
       "tail          446828\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Count null values\n",
    "dfResultsFinal.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop null records.\n",
    "dfTrain = dfResultsFinal.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Our Final dataset contains about 120 mio triples or statements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 119976844 entries, 0 to 141206852\n",
      "Data columns (total 3 columns):\n",
      " #   Column    Dtype \n",
      "---  ------    ----- \n",
      " 0   head      object\n",
      " 1   relation  object\n",
      " 2   tail      object\n",
      "dtypes: object(3)\n",
      "memory usage: 3.6+ GB\n"
     ]
    }
   ],
   "source": [
    "dfTrain.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's split our data into training, test and valid datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "trainDF, testDF = train_test_split(dfTrain, test_size=0.1)\n",
    "\n",
    "trainDF, validDF = train_test_split(trainDF, test_size=0.15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((91782285, 3), (16196874, 3), (11997685, 3))"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainDF.shape, validDF.shape, testDF.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Let's save the our files as tabular separated files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "trainDF.to_csv(\"./data/wikimedia/train.txt\", header = None, index = None, sep = \"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "validDF.to_csv(\"./data/wikimedia/valid.txt\", header = None, index = None, sep = \"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "testDF.to_csv(\"./data/wikimedia/test.txt\", header = None, index = None, sep = \"\\t\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Knowledge Graph Embeddings "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Let's run the command line to generage our embeddings. In our case we will mix cpu-gpu as our KG is quite large."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "!DGLBACKEND=pytorch dglke_train --dataset wikimedia --model_name TransE_l2 --batch_size 1000 \\\n",
    "--neg_sample_size 200 --hidden_dim 400 --gamma 19.9 --lr 0.25 --max_step 24000 --log_interval 100 \\\n",
    "--batch_size_eval 16 -adv --regularization_coef 1.00E-09 --test --gpu 0 1 2 3 --mix_cpu_gpu  --save_path ./wikimedia \\\n",
    "--data_path ./data/wikimedia/ --format raw_udd_hrt --data_files train.txt valid.txt test.txt --neg_sample_size_eval 10000 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Let's evaluate our model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "!DGLBACKEND=pytorch dglke_eval --dataset wikimedia --model_name TransE_l2 \\\n",
    "--neg_sample_size 200 --hidden_dim 400 --gamma 19.9 \\\n",
    "--batch_size_eval 16 --gpu 0 1 2 3  --model_path ./wikimedia/TransE_l2_wikimedia_0/ \\\n",
    "--data_path ./data/wikimedia/ --format raw_udd_hrt --data_files train.txt valid.txt test.txt --neg_sample_size_eval 10000 --no_eval_filter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "Let's run the inference command line. In our case we want to find the Top 5 similar nodes to our \"head.list\" file. Our file contains the following entries:\n",
    "\n",
    "Jeff Bezos<br>\n",
    "Barack Obama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using PyTorch Backend\n",
    "!DGLBACKEND=pytorch dglke_emb_sim --model_path ./wikimedia/TransE_l2_wikimedia_0/ \\\n",
    "--format 'h_*_*' --data_files head.list \\\n",
    "--score_func none --topK 10 --gpu 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's inspect our results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "left\tright\tscore\n",
      "Jeff Bezos\tJeff Bezos\t1.0\n",
      "Jeff Bezos\tAga Khan IV\t0.8602205514907837\n",
      "Jeff Bezos\tAlisher Usmanov\t0.8584005236625671\n",
      "Jeff Bezos\tKlaus Tschira\t0.8512368202209473\n",
      "Jeff Bezos\tBill Gates\t0.8441287875175476\n",
      "Jeff Bezos\tMatthew McDowell\t0.8408565521240234\n",
      "Jeff Bezos\tAl-Waleed bin Talal\t0.8397267460823059\n",
      "Jeff Bezos\tAndreas Heldal-Lund\t0.8363775014877319\n",
      "Jeff Bezos\tRinat Akhmetov\t0.8358486294746399\n",
      "Jeff Bezos\tOleg Deripaska\t0.831737220287323\n",
      "Barack Obama\tBarack Obama\t1.0\n",
      "Barack Obama\tDonald Trump\t0.9529082179069519\n",
      "Barack Obama\tGeorge W. Bush\t0.9426612854003906\n",
      "Barack Obama\tHarry S. Truman\t0.9414601922035217\n",
      "Barack Obama\tRonald Reagan\t0.9393566250801086\n",
      "Barack Obama\tBill Clinton\t0.9360300898551941\n",
      "Barack Obama\tFranklin Delano Roosevelt\t0.9323158264160156\n",
      "Barack Obama\tGeorge H. W. Bush\t0.9265057444572449\n",
      "Barack Obama\tDwight D. Eisenhower\t0.9219849109649658\n",
      "Barack Obama\tGerald Ford\t0.9207168221473694\n"
     ]
    }
   ],
   "source": [
    "!cat result.tsv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  },
  "nteract": {
   "version": "0.23.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
